Excel Export Plus settings

The Excel Export Plus function enables different types of lists to be generated in Microsoft Excel format: material lists, cutting lists, and so on. The system contains one default report profile that you can use to generate a sample report, and then from the generated report you can access tools that allow creating customized reports for different purposes.

Excel Export Plus is a separately licensed module that must explicitly be selected when installing the software.

Users must have Microsoft Excel with Visual Basic for Application (VBA) installed to use this function, and Excel’s macro security settings must allow execution of macros. If eBrowser integration is to be used, the 32-bit version of eBrowser must be installed in the same computer as Plant Modeller, and an eBrowser model must be published from the project.

For information on how to generate reports, see Excel Export Plus.

The following topics describe how to perform administrative actions on the reports.

CADMATIC Menu in Microsoft Excel

In a Microsoft Excel file generated with Excel Export Plus, the Add-Ins tab has a CADMATIC Menu button that allows you to run various commands.

The commands are:

  • Report Control – Opens the Report Control dialog for defining how the report is generated. See CADMATIC Menu > Report Control.

  • Columns Hide / Show – Opens the Select Columns dialog where you can define what columns to show in the data sheets of the report.

  • Sheet selection – Lists the data sheets of the report. Select a sheet from the list to display that sheet. The same list can also be found from the TOTAL sheet of the report file.

  • Write settings to a file (a Report Profile) – Saves the report settings in a profile file (*.rpp); create a separate profile for each different type of report that you want to generate. These files are stored in the ExcelExportPlus subfolder of the $PMS_CONFDIR directory. Typically, this is <CADMATIC software>\<computer name>.stc\ExcelExportPlus\*.rpp, but it is also possible to store this folder in the subdirectory <ProjectName>\*.rpp. The Excel template is taken from <CADMATIC software>\pms<software version>\opt\pm\ExcelExportPlus\ExcelExportPlus_template.xlsm, or from the previously mentioned ExcelExportPlus directory.

  • eBrowser link – Opens a file browser for linking the report to an eBrowser model.

  • Clean up – This option deletes the internal database that the report file uses and removes CADMATIC Menu from the report. This action results in a standard Excel file that still contains the same data as before (a TOTAL sheet and one or more data sheets), but you can no longer regenerate the data with different settings.

  • Export to a textfile (*.csv) – Saves the report settings in a text file (*.csv). To do this, you must first define the export settings in Report Control > Settings > Export to CSV.

CADMATIC Menu > Report Control

Selecting Report Control from the CADMATIC Menu of a report created with Excel Export Plus opens the Report Control dialog where you can customize the report, generate a new report with the current settings, and then possibly save the settings in a new report profile.

DISTRIBUTE TO SHEETS

In the DISTRIBUTE TO SHEETS pane you can select whether you want to list all objects in one excel sheet (option set to “None”), or distribute the objects to several sheets, according the values of a specific column. For instance, all objects that belong to the same System could be listed on a separate sheet. If you change this setting and regenerate the report, all existing data sheets are deleted before adding new ones according to the categorization you selected.

SUMMING RULES

The SUMMING RULES settings are used to determine which rows should be merged to one row. For instance, all objects that have the same position ID or PartID could be merged into one row.

There are three criterion rows that define which columns to use for this. These are so-called summing rules.

If you use two summing rules, for instance “Pipeline No” and “PartID”, then all objects will be merged to one row that has the same ‘Pipeline Number’ AND the same ‘part ID’.

In general, for every row that has the same values of all three selected summing rules will be merged together to one Row.

When objects are merged to the same row, the quantity field is updated accordingly. If 3 objects are merged into one row, the quantity is set to 3.

These fields are summed when rows are merged into one:

  • length fields (len, bpl)
  • Quantity
  • Weights (Weight, Content Weight, Total Weight)
  • Centre of Gravities (CGX, CGY, CGZ, content COG's, Tot. COG's)
  • Any other field that is marked to be summed [Sum: yes] in the properties of this column (via Settings > Edit Row).

The columns that are not summed are emptied in case the values of the merged rows differ. For instance when two objects are summed according to description, but each object has a unique positionId, then the merged row will not show any positionID value.

SORTING RULES

The SORTING RULES settings are used to determine the order in which the rows will be listed. First the list will be sorted according to the column selected in "Sorting 1.". If column values are the same, then "Sorting 2." will be used, and again if column values are the same "Sorting 3." will be used.

CADMATIC Menu > Report Control > Settings

In the Report Control dialog, click Settings to open the Settings dialog.

Columns tab

The Columns tab lists the columns and information about them. At the top you can select whether to show just those columns that are visible in the current report, or just those columns that have customizable tags, or all columns.

Columns whose tags you cannot customize contain information that is used in other places. For example, Weight is used to calculate Tot.Weight, and therefore Weight is not customizable.

To change the definition of a column, double-click the column’s row, or select the row and click Edit Row. The Edit row dialog opens.

Edit row settings

  • Title – The description of this column (the name of that column).

  • Visibility – This field is disabled; you can only change visibility from the CADMATIC Menu option Columns Show / Hide.

  • Format – Specifies column format. If you set this to “formula”, you can use all kinds of formulas that Excel supports. For example, you can use [R] to fill in the row number; to extract the value from column, you can use the formula =A[R]. Other examples:

    =A[R] & B[R] to combine text of column A and column B
    =C[R] + D[R] to add the values of column C and column D
    =IF(B[R]=C[R],"ok", "not ok")
    ="Project is: " & A$8 (value of some specific field A8. notice the "$")

    Note: When using operators such as IF in the formula, you must use the original (English) operators, and not any localized versions of the operators.

  • Column Width – Autofit, Fixed, Min., or Max.

    “Autofit” allows Excel to define the column width according to the length of the column’s values. “Fixed” you might want to select especially when you use a pre-defined layout, so that the width is always the same and the report fits on paper. You can either enter the value to use or copy the current width from the Excel sheet by clicking the Width button. “Min.” and “Max.” are similar to Autofit, but you specify a minimum or maximum width for the column.

o Tags

In general, Outfitting objects have all kind of additional data stored to that object. These data can be stored via attributes which are attached to these objects. For example the MASS, the name, systemID, etc.

Every attribute is known via its abbreviation (usually 2 or 3 characters). This abbreviation is a TAG. In the TAG fields of this panel you can fill in one or more TAGS. More tags can be used for instance to collect different kinds of data and combine them into one field on the report list.

Also it is possible to put TEXT here in the field if it begins and ends with a quote ('). For instance, you can put the Dutch description (dud) and the normal description (DE) to the list with a slash between the descriptions:

TAG'S: dud+'/'+DE

Also it is possible to put for instance the length in the field but if the objects has no length then put the quantity. It can look like this:

TAG's: len

Alternative Tag: qty

The ‘alternative tags’ are only used in case the normal tag of that object has an empty value.

The data will be retrieved from CADMATIC Outfitting. So changing this value will not affect current report directly. Only when the settings are saved to the settings via menu-item “Write settings to a file (Report Profile)”. When a new report is created from Plant Modeller using the changed profile, you will see the results of the changes in your report.

Most of the data can be retrieved from the Plant Modeller application. If you choose to use a TAG that is retrieved from Pipe (iso/spool), then bear in mind that the report generation might be much slower because the program has to check all objects in the iso and spool drawings one by one.

o Tags retrieved from:

Tag-information will normally be extracted from Plant Modeller.

This will attempt to read the tag not only from the 3D object, but also from the referenced Corporate Catalog. COS attributes and COS properties can be extracted as well, but this is not recommended as it causes slowness to COS server.

It is also possible to extract tags from the project attributes or from the iso or spool drawings (from its material file or header data).

Tags retrieved from Pipe will make the generation of the lists slower (because it takes more time to retrieve those from iso and spools).

o At slave objects Retrieve tags from it’s master

A Master object can refer to a slave object. A slave object is not a real 3D object, but only an object from the library.

Depending on the usage, sometimes the tags of these slaves should be retrieved from its master (like pipeline info), and sometimes the tags should be retrieved from the slave part itself (especially corporate catalog tags, like article code or Material).

Layout tab

On the Layout tab, you can customize the layout of the report. Select a sheet from the list to specify its settings.

The Select Layout Sheet drop-down menu lists the available layouts. LAYOUT1, LAYOUT2, and LAYOUT3 are default, hidden layout sheets defined in the file ExcelExportPlus_template.xlsm. If the file contains any other sheets whose name starts with LAYOUT, they are listed in the drop-down menu. Accordingly, to create a custom report layout, you must first add a new sheet using a name such as LAYOUT4, design the header block in the sheet, and then you can adjust its other settings on this Layout tab.

Number of Rows and Columns on layout specifies the starting point of data rows below the header block. If the header block contains 16 rows of information, enter Rows: 17, Columns: 1 to specify that the actual list starts from row 17, column 1. The same can be achieved by leaving the Rows and Columns values to zero, and entering the word DATA in the specified cell of the template layout sheet.

In the Layout Sheet Settings pane, you can specify the following settings.

  • Hide Titlerow – If selected, the system does not add a title row to the report file. Select this option if a title row is defined in the layout sheet.
  • Retrieve Width of Columns from columns of layout sheet – If selected, column width is taken from the layout sheet.
  • Fit width to one page (via scaling) – If selected, the system scales the report to fit on one page.
  • Add row with Excel Autofilter – If selected, a filter row is added to the report.

In the Layout Header & Footer texts pane, you can specify the header and footer texts. You can use:

  • Free text

  • Any environment or project attribute, prefixed with the Dollar character: $PMS_PROJNAME.

  • Standard Excel definitions, such as:

    • &B for Bold text
    • &F for File name
    • &D for Date
    • &P for Page

Total Fields tab

On the Total Fields tab, you can specify total values to be calculated and displayed in the report.

Add Total fields – If selected, the total value of Mass, Length, and Center of Gravity is calculated and displayed at the bottom of the respective columns. Additionally, you can select to include content weight in the calculation.

Add Summary Sheet – If selected, the total values of Mass, Length, and Center of Gravity is calculated and displayed on a separate sheet. Additionally, you can select to include content weight in the calculation.

Custom Total fields – To specify additional columns with numeric values for which a total value should be calculated and displayed in the report.

Miscellaneous I tab

On the Miscellaneous I tab, you can specify the following settings.

  • Target Directory Reportfile – To specify the location where the report file is generated.
  • Counter Column – To specify a column that should contain a running number.
  • Lengths in Meters or Millimeters – By default lengths are expressed in millimeters. Select Unit in Meters to express lengths in meters instead.

Miscellaneous II tab

On the Miscellaneous II tab, you can specify the following settings.

  • Gaskets – To include gaskets in the report. Note that this might cause the report to be generated more slowly.
  • Include Slaveparts – To include slave parts in the report.
  • Close Excel – If selected, the Excel file is automatically closed after generating it.
  • Delete sum-file – If selected, after generating the report the system deletes the *.sum file that it used for creating the report.
  • Add quantity – If selected, a quantity value is added to objects that do not have a quantity value by default (typically, that is because they are measured in lengths instead of quantities).

Export to CSV tab

On the Export to CSV tab, you can specify settings that affect the CSV export that you can perform from the CADMATIC Menu of the report file.

  • Export to CSV-file – If selected, the report can also be generated into a text file. By default the separator character is semicolon, but you can specify a different separator character in the Separator sign field.
  • Columns included in the CSV-file – Enter the columns to include in the CSV file, using the internal column names such as COL_SYS for System.
  • CSV Export Directory – To specify the location where the report file is generated.
  • Add Headerline to CSV-file – If selected, a header row is added to the CSV file.
  • Skip double-quotes around values – If selected, values are not enclosed in double quotes.
  • Enforce a Dot as Decimal Separator – If selected, the dot (period) character is used as decimal separator, and the regional settings that otherwise specify this character are ignored.

Tips and tricks

  • There are only three summing rules and three sorting rules. This is because internally Excel can only sort on three criteria at a time. But, you can actually use any amount of summing rules by just combining columns. For instance, you can make a dummy column that contains the content of three other columns, and then use this dummy column in the summing rule. Then you only use one summing rule, and the other two are still available.
  • There is a predefined column called “SpoolObject”, and this can be used in a summing rule. This does not only check the spool numbers, but even groups objects that are not yet spool numbered. When generating the report, it uses the spool breaking rules to assign a temporal spool number that is then used in this column.
  • Some pipe parts have information about the Fabrication method (mth), like bendings or Tee's. In your report you can list the amount of bendings ($B) or Tee's ($T) found in the piperun. This can be done by using the attribute abbreviation: "bends" or "tees”.
  • The "SpoolObject" column contains a unique value for every object that belongs to the same spool, or that will belong to same spool (when spool numbered). So the values of this column are not that interesting, but this column is handy to use in the summing rules; in that way you can see which spools are actually ready and which are not.
  • The following possible custom attributes can be used:
    • rg0 (Status of the iso(!) doc: "Iso Needs Update" / "Iso is UpToDate")
    • rg1 (to extract the modification time of the spool-drawing)
    • rg2 (to extract the IsoDocname property COS_PROPERTY_CREATED_BY)
  • Special case: extracts COS attribute from corporate-catalog in case tag starts with "cc_".
  • Special case: when retrieving an attribute containing a COS id (for instance, cmd), it is possible to get the description of that COS object instead of its COS id. This can be done by adding “des_” in front of the abbreviation: des_cmd.
  • Instead of an attribute abbreviation, also the Dim name from DimensionTable can be extracted.